<?php defined ( 'SYSPATH' ) or die ( 'No direct access allowed.' );
/**
 * 不良品库存统计报表
 * Enter description here ...
 * @author locove
 *
 */
class Controller_Report_Wasterstock extends Controller_Admin
{
    public function before()
    {
    	try {
    		  ACL::Required('buhegestock report');
    	} catch ( HTTP_Exception_403 $e ) {
				Message::error('sorry，你没有权限进这个目录');
    		$this->request->redirect ( URL::base (true) );
    	}
        parent::before();
    }
    /**
     * 首页搜索界面
     * Enter description here ...
     */
	public function action_index()
	{
		$this->title=__("不良品库存统计");
		$view = View::factory('report/wasterstock');
		$this->response->body($view);
	}
	/**
	 * 获取分页不良品库存统计信息
	 */
	public function action_wasterstock_load()
	{
		$result=array();
		$offset=0;
		$items_per_page=100;
	 	$and_where=$orderby='';
		if(isset($_GET['page'])&&$_GET['page']){
	 		$offset=($_GET['page']-1)*$items_per_page;
		}
		$and_where_1=$and_where_2=$and_where_3='';
		if(isset($_GET['sku'])&&$_GET['sku']){
			$and_where_1=" AND sku='".$_GET['sku']."'";
		}
		if(isset($_GET['check_time'])&&$_GET['check_time']){
			$and_where_2=" AND check_time like '".$_GET['check_time']."%'";
			$and_where_3=" AND out_time like '".$_GET['check_time']."%'";
		}
		$count=0;
		if($and_where_1){
			$sql="select sum(quantity) as suminquantity 
				from unqualified_deal_invoices 
				where 1 $and_where_1 $and_where_2";
			$sum=DB::query(Database::SELECT, $sql)->execute('newerp')->current();
			if(isset($sum['suminquantity'])){
				$count=1;
				$result[$_GET['sku']]['inquantity']=$sum['suminquantity']; 
				$sql="select sku,sku_name from unqualified_deal_invoices where 1 $and_where_1 $and_where_2 limit 1";
				$ins=DB::query(Database::SELECT, $sql)->execute('newerp')->current();
				if(isset($ins['sku'])){
					$result[$_GET['sku']]['sku_name']=$ins[0]['sku_name'];
					$sql="select sum(quantity) as sumoutquantity from bad_product_delivery where 1 AND status=20 $and_where_1 $and_where_3";
					$outs=DB::query(Database::SELECT, $sql)->execute('newerp')->current();
					if(isset($outs['sumoutquantity'])){
						$result[$_GET['sku']]['outquantity']=$outs['sumoutquantity'];
					}else{
						$result[$_GET['sku']]['outquantity']=0;
					}
					$result[$_GET['sku']]['nowquantity']=$result[$_GET['sku']]['inquantity']-$result[$_GET['sku']]['outquantity'];
				}
			}
		}else{
			$sql="select * from unqualified_deal_invoices where 1 $and_where_2 group by sku";
			$num=DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
			$count=count($num);
			$sql="select sum(quantity) as suminquantity,sku,sku_name from unqualified_deal_invoices where 1 $and_where_2 group by sku";
			$sum=DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
			if(isset($sum[0]['suminquantity'])){
				foreach($sum as $_sum){
					$result[$_sum['sku']]['sku_name']=$_sum['sku_name'];
					$result[$_sum['sku']]['inquantity']=$_sum['suminquantity'];
				}
				$sql="select sku,sum(quantity) as sumoutquantity from bad_product_delivery where 1 AND status=20 $and_where_3 group by sku";
				$outs=DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
				if(count($outs)>0){
					foreach($outs as $_out){
						$result[$_out['sku']]['outquantity']=$_out['sumoutquantity'];
					}
				}
				foreach ($result as $key=>$_result){
					if(isset($_result['outquantity'])&&isset($result[$key]['inquantity'])){
						$result[$key]['nowquantity']=$result[$key]['inquantity']-$result[$key]['outquantity'];
					}elseif(isset($result[$key]['inquantity'])){
						$result[$key]['outquantity']=0;
						$result[$key]['nowquantity']=$result[$key]['inquantity'];
					}elseif(isset($result[$key]['outquantity'])){
						$result[$key]['inquantity']=0;
						$result[$key]['nowquantity']=-$result[$key]['outquantity'];
					}else{
						$result[$key]['outquantity']=0;
						$result[$key]['inquantity']=0;
						$result[$key]['nowquantity']=0;
					}
				}
			}
		}
		$_result=array_slice($result,$offset,100);
 		$view=View::factory('report/wasterstock_table')
 			->set('wasterstocks',$_result)
 			->set('totles',$count)
 			->render();
 		echo $view;
	}	
	/**
	 * 下载报表
	 */
	public function action_down()
	{
 		set_time_limit(0);
 		ini_set('memory_limit','512M');
 		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
 		$this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
 		$objPHPExcel      = new PHPExcel();
 		$objPHPExcel->getProperties()->setCreator("YKS")
 		->setLastModifiedBy("YKS")
 		->setTitle("Office 2003 XLSX Test Document")
 		->setSubject("Office 2003 XLSX Test Document")
 		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
 		->setKeywords("office 2003 openxml php")
 		->setCategory("Test result file");
 		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
 		$objActSheet->setCellValue('A1', 'SKU');
 		$objActSheet->setCellValue('B1', '中文名称');
 		$objActSheet->setCellValue('C1', '入库量');
 		$objActSheet->setCellValue('D1', '出库量');
 		$objActSheet->setCellValue('E1', '实际库存量');
		$and_where=$orderby='';
		
		$result=array();
		$and_where_1=$and_where_2=$and_where_3='';
		if(isset($_GET['sku'])&&$_GET['sku']){
			$and_where_1=" AND sku='".$_GET['sku']."'";
		}
		if(isset($_GET['check_time'])&&$_GET['check_time']){
			$and_where_2=" AND check_time like '".$_GET['check_time']."%'";
			$and_where_3=" AND out_time like '".$_GET['check_time']."%'";
		}
		$count=0;
		if($and_where_1){
			$sql="select sum(quantity) as suminquantity 
				from unqualified_deal_invoices 
				where 1 $and_where_1 $and_where_2";
			$sum=DB::query(Database::SELECT, $sql)->execute('newerp')->current();
			if(isset($sum['suminquantity'])){
				$count=1;
				$result[$_GET['sku']]['inquantity']=$sum['suminquantity']; 
				$sql="select sku,sku_name from unqualified_deal_invoices where 1 $and_where_1 $and_where_2 limit 1";
				$ins=DB::query(Database::SELECT, $sql)->execute('newerp')->current();
				if(isset($ins['sku'])){
					$result[$_GET['sku']]['sku_name']=$ins[0]['sku_name'];
					$sql="select sum(quantity) as sumoutquantity from bad_product_delivery where 1 AND status=20 $and_where_1 $and_where_3";
					$outs=DB::query(Database::SELECT, $sql)->execute('newerp')->current();
					if(isset($outs['sumoutquantity'])){
						$result[$_GET['sku']]['outquantity']=$outs['sumoutquantity'];
					}else{
						$result[$_GET['sku']]['outquantity']=0;
					}
					$result[$_GET['sku']]['nowquantity']=$result[$_GET['sku']]['inquantity']-$result[$_GET['sku']]['outquantity'];
				}
			}
		}else{
			$sql="select * from unqualified_deal_invoices where 1 $and_where_2 group by sku";
			$num=DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
			$count=count($num);
			$sql="select sum(quantity) as suminquantity,sku,sku_name from unqualified_deal_invoices where 1 $and_where_2 group by sku";
			$sum=DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
			if(isset($sum[0]['suminquantity'])){
				foreach($sum as $_sum){
					$result[$_sum['sku']]['sku_name']=$_sum['sku_name'];
					$result[$_sum['sku']]['inquantity']=$_sum['suminquantity'];
				}
				$sql="select sku,sum(quantity) as sumoutquantity from bad_product_delivery where 1 AND status=20 $and_where_3 group by sku";
				$outs=DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
				if(count($outs)>0){
					foreach($outs as $_out){
						$result[$_out['sku']]['outquantity']=$_out['sumoutquantity'];
					}
				}
				foreach ($result as $key=>$_result){
					if(isset($_result['outquantity'])&&isset($result[$key]['inquantity'])){
						$result[$key]['nowquantity']=$result[$key]['inquantity']-$result[$key]['outquantity'];
					}elseif(isset($result[$key]['inquantity'])){
						$result[$key]['outquantity']=0;
						$result[$key]['nowquantity']=$result[$key]['inquantity'];
					}elseif(isset($result[$key]['outquantity'])){
						$result[$key]['inquantity']=0;
						$result[$key]['nowquantity']=-$result[$key]['outquantity'];
					}else{
						$result[$key]['outquantity']=0;
						$result[$key]['inquantity']=0;
						$result[$key]['nowquantity']=0;
					}
				}
			}
		}
		$i=0;
        foreach($result as $_key=>$_result){
 			$key=$i+2;
 			$i++;
 			$objActSheet->setCellValue('A'.$key, $_key);
			$_result['sku_name']=isset($_result['sku_name'])?$_result['sku_name']:'';
	 		$objActSheet->setCellValue('B'.$key, $_result['sku_name']);
	 		$objActSheet->setCellValue('C'.$key, $_result['inquantity']);
			$objActSheet->setCellValue('D'.$key, $_result['outquantity']);
	 		$objActSheet->setCellValue('E'.$key, $_result['nowquantity']);
			unset($_result['sku_name']);unset($_result['inquantity']);unset($_result['outquantity']);unset($_result['nowquantity']);
 		}
 		$objPHPExcel->getActiveSheet()->setTitle('不良品库存统计报表');
		$objPHPExcel->setActiveSheetIndex(0);
 		$day=date("m-d");
 		$filename=$day.'不良品库存统计报表.xls';
        ob_end_clean();
		header("Content-Type: application/vnd.ms-excel; charset=utf-8");
		header('Content-Disposition: attachment;filename='.$filename);
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;		
	}	
}
